package com.web.bdo.dao.util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionMessage;

import com.web.bdo.User;
import com.web.util.date.DateUtil;
import com.web.util.sql.DatabaseConnector;

public class UserDaoUtil {

	public static boolean isAvailable(String emailAddress) {
		
		String id = null;
		PreparedStatement pStat = null;
		ResultSet rs = null;
		String sql = "SELECT [id] FROM [FlyMyWay].[dbo].[tblUsers] WHERE [emailAddress] = ? ";
		
		try {
			pStat = DatabaseConnector.getConnection().prepareStatement(sql);
			pStat.setString(1, emailAddress);
			rs = pStat.executeQuery();
			if (rs.next()) {
				id = rs.getString("id"); //$NON-NLS-1$
			}			
			if (id==null) {
				return true;
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return false;
	}
	
	public static ActionErrors login(User user) {
		
		ActionErrors errors = new ActionErrors();
		PreparedStatement pStat = null;
		ResultSet rs = null;
		String sql = "SELECT [id], [emailAddress], [password], [firstName], [lastName], [sex], [age], [countryCode], [dateCreated], [dateUpdated] FROM [FlyMyWay].[dbo].[tblUsers] WHERE [emailAddress] = ? AND [password] = ?";		
		
		try {
			pStat = DatabaseConnector.getConnection().prepareStatement(sql);
			pStat.setString(1, user.getEmailAddress());
			pStat.setString(2, user.getPassword());
			rs = pStat.executeQuery();			
			
			if (rs.next()) {
				if (rs!=null) {
					user.setId(rs.getString("id").trim());
					user.setEmailAddress(rs.getString("emailAddress").trim());
					user.setPassword(rs.getString("password").trim());
					user.setFirstName(rs.getString("firstName").trim());
					user.setLastName(rs.getString("lastName").trim());
					user.setSex(rs.getInt("sex"));
					user.setAge(rs.getInt("age"));
					user.setCountry(rs.getString("countryCode").trim());
					user.setDateCreated(DateUtil.toDate("yyyy-MM-dd hh:mm:ss.0", rs.getString("dateCreated")));
					user.setDateUpdated(DateUtil.toDate("yyyy-MM-dd hh:mm:ss.0", rs.getString("dateUpdated")));								
				}
				else {
					errors.add("invalidLogin", new ActionMessage("errors.login.invalidLogin")); //$NON-NLS-1$ //$NON-NLS-2$					
				}
			}
			else {
				errors.add("invalidLogin", new ActionMessage("errors.login.invalidLogin")); //$NON-NLS-1$ //$NON-NLS-2$
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			errors.add("userDatabase", new ActionMessage("errors.login.sql")); //$NON-NLS-1$ //$NON-NLS-2$
		}		
		return errors;
	}
	
	public static void reload(User user) {
		
		PreparedStatement pStat = null;
		ResultSet rs = null;
		String sql = "SELECT [id], [emailAddress], [password], [firstName], [lastName], [sex], [age], [countryCode], [dateCreated], [dateUpdated] FROM [FlyMyWay].[dbo].[tblUsers] WHERE [id] = ?";
		
		try {
			pStat = DatabaseConnector.getConnection().prepareStatement(sql);
			pStat.setString(1, user.getId());			
			rs = pStat.executeQuery();
			
			if (rs.next()) {
				if (rs!=null) {
					user.setId(rs.getString("id").trim()); //$NON-NLS-1$
					user.setEmailAddress(rs.getString("emailAddress").trim()); //$NON-NLS-1$
					user.setPassword(rs.getString("password").trim()); //$NON-NLS-1$
					user.setFirstName(rs.getString("firstName").trim()); //$NON-NLS-1$
					user.setLastName(rs.getString("lastName").trim()); //$NON-NLS-1$
					user.setSex(rs.getInt("sex")); //$NON-NLS-1$
					user.setAge(rs.getInt("age")); //$NON-NLS-1$
					user.setCountry(rs.getString("countryCode").trim()); //$NON-NLS-1$
					user.setDateCreated(DateUtil.toDate("yyyy-MM-dd hh:mm:ss.0", rs.getString("dateCreated")));
					user.setDateUpdated(DateUtil.toDate("yyyy-MM-dd hh:mm:ss.0", rs.getString("dateUpdated")));					
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();			
		}
	}
}
